package com.wuseguang.springcloud.service.impl;

import com.wuseguang.springcloud.Classcardservice.ClassCardFieldService;
import com.wuseguang.springcloud.Classcardservice.ClassCardService;
import com.wuseguang.springcloud.dao.jdbc.ClassPicturesJdbc;
import com.wuseguang.springcloud.dao.jdbc.ClassesJdbc;
import com.wuseguang.springcloud.dao.mapper.ClassPicturesMapper;
import com.wuseguang.springcloud.dao.repository.ClassPicturesRepository;
import com.wuseguang.springcloud.dao.repository.PicturesRepository;
import com.wuseguang.springcloud.entity.ClassPictures;
import com.wuseguang.springcloud.entity.Pictures;
import com.wuseguang.springcloud.service.ClassPicturesService;
import com.wuseguang.springcloud.util.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.util.*;

/**
 * <p>
 * 班级(相片/图片) 服务实现类
 * </p>
 *
 * @author
 * @since 2020-01-07
 */
@Service
public class ClassPicturesServiceImpl  implements ClassPicturesService {


    private Logger logger = LoggerFactory.getLogger(this.getClass());
    @Autowired
    private ClassPicturesJdbc classPicturesJdbc;
    @Autowired
    private ClassPicturesMapper classPicturesMapper;
    @Autowired
    private ClassPicturesRepository classPicturesRepository;
    @Autowired
    private PicturesRepository picturesRepository;
    @Autowired
    private ClassesJdbc classesJdbc;

    @Autowired
    private ClassCardFieldService service;

    @Autowired
    private ClassCardService classCardService;

    @Override
    public Result addClassPictures(String schoolId, String name, Integer show, Integer pshow, Integer[] classId, String pictyresUrl, String termId, String publisher, String teacherId) {

        //创建图片/相片实体
        Pictures pictures = new Pictures();
        //图片名称/图片标题
        pictures.setName(name);
        //图片url
        pictures.setPictyresUrl(pictyresUrl);
        //发布时间
        pictures.setOpertime(new Date());
        //学校id
        pictures.setSchoolId(Integer.valueOf(schoolId));
        //来源
        pictures.setSource(1);
        //学期
        pictures.setTermId(Integer.valueOf(termId));
        //是否审核 (后期扩展审核功能，现在默认审核)
        pictures.setChecks(0);
        //是否显示到班牌首页
        pictures.setDisplay(show);
        //家长端小程序是否展示
        pictures.setPdisplay(pshow);
        //发布人
        pictures.setPublisher(publisher);
        //发布人id
        pictures.setTeacherId(Integer.valueOf(teacherId));
        //添加并且获得id
        Integer picturesId = picturesRepository.save(pictures).getId();
        //如果为空
        if (StringUtils.isEmpty(picturesId)) {
            return new Result(CodeUtil.IMAGE_PUBLISHING_FAILED);
        }

        String sql = "insert into `system_notification_form` (`type`, `icon`, `publisher_id`, `publisher`, `content`, `class_id`,`opertime`) values(?,?,?,?,?,?,now())";
        //班级(相片/图片)集合
        List<ClassPictures> classPicturesList = new ArrayList<ClassPictures>();
        //循环每个班级，跟发布的图片进行绑定
        for (int i = 0; i < classId.length; i++) {
            //创建班级图片
            ClassPictures classPictures = new ClassPictures();
            //班级id
            classPictures.setClassId(classId[i]);
            //图片id
            classPictures.setPicturesId(picturesId);
            //不置顶
            classPictures.setTop(0);
            //学校id
            classPictures.setSchoolId(Integer.valueOf(schoolId));
            //添加到集合中稍后批量添加
            classPicturesList.add(classPictures);

            //添加通知
            classesJdbc.updateByParameter(sql, 1, "https://oss.yuguozhijia.online/icon/%E5%9B%BE%E7%89%87%E7%AE%A1%E7%90%86.png", teacherId, publisher, name, classId[i]);
            //修改心跳字段 圖片
            service.classesAndCards(classId[i], "picturesflag", "1");
            //修改心跳字段  通知
            service.classesAndCards(classId[i], "noticeflag", "1");
        }

        classPicturesRepository.saveAll(classPicturesList);

        return new Result();
    }

    @Override
    public Result deletePictures(String id, String teacherId, String admin) {

        try {
            //修改心跳字段
            String sql1 = "SELECT class_id FROM class_pictures WHERE `pictures_id` = ?";
            List<Integer> integers = classesJdbc.queryListInteger(sql1, new Object[]{Integer.valueOf(id)});
            service.classesAndCards(integers, "picturesflag", "1");

            logger.info("删除图片");
            //根据id查询图片
            Pictures pictures = picturesRepository.findById(Integer.valueOf(id)).get();

            //判断如果是发布人删除全部
            if (teacherId.equals(pictures.getTeacherId().toString()) || "0".equals(admin)) {
                logger.info("这里是发布人或者管理员");
                //删除班级图片数据
                picturesRepository.deleteById(Integer.valueOf(id));
                //删除所有绑定视频的班级
                String sql = "DELETE FROM class_pictures WHERE `pictures_id` = ? ";
                classPicturesJdbc.updateByParameter(sql, id);

                classCardService.classesAndCards(integers);
                //删除oss中的文件视频
                CallbackSample.deleteFile(pictures.getPictyresUrl());
                return new Result();
            }

            //查询这个教室是不是年级组长
            List<String> groupLeader = classPicturesJdbc.findGradeLeader(teacherId);
            //判断集合大于0时进来
            if (groupLeader.size() > 0) {
                logger.info("年级组长");
                //删除这个图片的班级
                String sql = "DELETE FROM `class_pictures` WHERE `class_id`  in (select id from classes where grade_id = ?)";
                //循环
                for (String gradeId : groupLeader) {
                    classPicturesJdbc.updateByParameter(sql, gradeId);
                }
                //再次查询这个图片是否还有班级
                sql = "select COUNT(1) count from class_pictures where pictures_id = ? ";
                //获取还有多少条
                int count = classPicturesJdbc.queryCount(sql, id);
                //判断如果没有班级绑定这个图片了就把这个图片删除掉
                if (count == 0) {
                    picturesRepository.deleteById(Integer.valueOf(id));
                    //删除oss中的文件视频
                    CallbackSample.deleteFile(pictures.getPictyresUrl());
                }
                classCardService.classesAndCards(integers);
                return new Result();
            }
        } catch (Exception e) {

        }

        logger.info("无权限删除");
        return new Result(CodeUtil.NO_PUBLISHER_NO_CAO_ZU);
    }

    @Override
    public Result roofPlacementPictures(String id, String teacherId, String admin, String schoolId) {

        //修改心跳字段
        String sql1 = "SELECT class_id FROM class_pictures WHERE `pictures_id` = ?";
        List<Integer> integers = classesJdbc.queryListInteger(sql1, new Object[]{Integer.valueOf(id)});
        service.classesAndCards(integers, "picturesflag", "1");

        logger.info("置顶图片 ClassPicturesServiceImpl - > roofPlacementPictures");

        //根据id查询图片
        Pictures pictures = picturesRepository.findById(Integer.valueOf(id)).get();

        //判断是否是发布人
        if (teacherId.equals(pictures.getTeacherId().toString())) {
            logger.info("这里是发布人");
            //查询这个图片的id
            String sql = "select class_id string from class_pictures where pictures_id = ?";
            String classid = classPicturesJdbc.queryString(sql, id);
            logger.info("classid = {}", classid);

            //把这些班级的置顶全部改为0
            sql = "UPDATE `class_pictures` SET `top` = 0 WHERE `class_id` in (?)";
            classPicturesJdbc.updateByParameter(sql, classid);

            //把这些班级的置顶全部改为1
            sql = "UPDATE `class_pictures` SET `top` = 1 WHERE `pictures_id` = ?";
            classPicturesJdbc.updateByParameter(sql, id);
            classCardService.classesAndCards(integers);
            return new Result();
        }

        //查询这个教室是不是年级组长
        List<String> groupLeader = classPicturesJdbc.findGradeLeader(teacherId);
        //判断集合大于0时进来
        if (groupLeader.size() > 0) {
            logger.info("年级组长");
            //修改这些班级的置顶
            String sqlOne = "UPDATE `class_pictures` SET `top` = 0 WHERE `class_id`  in (select id from classes where grade_id = ?)";
            //把这些班级的还有符合图片id的班级置顶
            String sqlTwo = "UPDATE `class_pictures` SET `top` = 1 WHERE `class_id`  in (select id from classes where grade_id = ?) and pictures_id =?";
            //循环
            for (String gradeId : groupLeader) {
                //把这些班级改为不置顶
                classPicturesJdbc.updateByParameter(sqlOne, gradeId);
                //把这个图片的班级改为置顶
                classPicturesJdbc.updateByParameter(sqlTwo, gradeId, id);
            }
            classCardService.classesAndCards(integers);
            return new Result();
        }

        //判断是否是管理员
        if ("0".equals(admin)) {
            //修改这些班级的置顶
            String sqlOne = "UPDATE `class_pictures` SET `top` = 0 WHERE `school_Id`  = ? and top =1";
            //把这些班级的还有符合图片id的班级置顶
            String sqlTwo = "UPDATE `class_pictures` SET `top` = 1 WHERE `school_Id`  = ? and pictures_id =?";
            //把学校置顶的数据改为不置顶
            classPicturesJdbc.updateByParameter(sqlOne, schoolId);
            //把学校这个的班级全部改置顶
            classPicturesJdbc.updateByParameter(sqlTwo, schoolId, id);
            classCardService.classesAndCards(integers);
            return new Result();
        }


        return new Result(CodeUtil.NO_AUTHORITY);
    }

    @Override
    public Result findClassPictureList(String[] classId, String termId, String schoolId, Integer indexNo, Integer rows, String admin, String teacherId) {
        logger.info("查询班级图片 ClassPicturesServiceImpl - > findClassPictureList");
        //查询符合班级的班牌图片信息
        String sql = null;
        //查询的班级
        String classid = null;
        //判断班级为空并且是管理员
        if (classId.length == 0 && "0".equals(admin)) {
            sql = "select p.id,p.`name` ,p.pictyres_url,DATE_FORMAT( p.opertime, '%Y-%m-%d' ) opertime,p.publisher,t.head_portrait,p.display `show`,p.pdisplay pshow from pictures p  left join teacher t on (t.id=p.teacher_id) where EXISTS" +
                    "(select * from class_pictures cp where p.id =cp.pictures_id )   and p.school_id = ?   ORDER BY p.id desc";

        }
        //判断如果上传班级为空
        else if (classId.length == 0) {
            classid = classesJdbc.findClassIds(schoolId, teacherId);
            sql = "select p.id,p.`name` ,p.pictyres_url,DATE_FORMAT( p.opertime, '%Y-%m-%d' ) opertime,p.publisher,t.head_portrait,p.display `show`,p.pdisplay pshow from pictures p  left join teacher t on (t.id=p.teacher_id) where EXISTS" +
                    "(select * from class_pictures cp where p.id =cp.pictures_id and  cp.class_id in (" + classid + ") )  and p.school_id = ?   ORDER BY p.id desc";

        } else {
            //传为String 类型
            classid = org.apache.commons.lang.StringUtils.join(classId, ",");
            sql = "select p.id,p.`name` ,p.pictyres_url,DATE_FORMAT( p.opertime, '%Y-%m-%d' ) opertime,p.publisher,t.head_portrait,p.display `show`,p.pdisplay pshow from pictures p  left join teacher t on (t.id=p.teacher_id) where EXISTS" +
                    "(select * from class_pictures cp where p.id =cp.pictures_id and  cp.class_id in (" + classid + ") )  and p.school_id = ?   ORDER BY p.id desc";

        }


        //查询图片信息
        List<Map<String, Object>> list = classPicturesJdbc.queryListMap(sql, schoolId);

        //
        for (Map<String, Object> map : list) {
            //查询图片的绑定班级信息
            sql = "select c.id,c.`name`  from classes c  WHERE EXISTS " +
                    "(select * from class_pictures cp WHERE  c.id=cp.class_id and  pictures_id = ?)";
            List<Map<String, Object>> classList = classPicturesJdbc.queryListMap(sql, map.get("id"));
            map.put("list", classList);
        }

        //创建分页工具
        PagingTool tool = new PagingTool();
        //放入页码
        tool.setIndexNo(indexNo);
        //放入每页显示数量
        tool.setRows(rows);
        //放入角色集合
        tool.setList(list);
        //返回数据
        return new Result(new PaginationUtil<>(list.size(), rows, tool.getList(), indexNo));
    }

    @Override
    public Result findPicturesVideoNotice(String schoolId, Integer findType, String startTime, String endTime) {


        String time = null;

        //判断查询类型
        if (findType == 1) {
            logger.info("查询上周数据");
            time = " and YEARWEEK(date_format(opertime,'%Y-%m-%d')) = YEARWEEK(now())-1 ";
        } else if (findType == 2) {
            logger.info("查询本月数据");
            time = " and date_format(opertime,'%Y-%m')=date_format(now(),'%Y-%m') ";
        } else if (findType == 3) {
            logger.info("查询自定义数据");
            time = " and  date_format(opertime,'%Y-%m-%d') between date_format('" + startTime + "','%Y-%m-%d') and date_format('" + endTime + "','%Y-%m-%d') ";
        } else {
            logger.info("查询本周数据");
            time = " and YEARWEEK(date_format(opertime,'%Y-%m-%d')) = YEARWEEK(now()) ";
        }

        logger.info("查询班级图片视频通知统计 ClassPicturesServiceImpl - > findPicturesVideoNotice");
        //统计学校的发布视频统计
        String videoSql ="SELECT 0 noticeCount, c.id classId , 0 picturesCount, COUNT(1) videoCount,v.opertime opertime FROM classes c " +
                " LEFT JOIN class_video cv ON ( c.id = cv.class_id ) LEFT JOIN grade g on(g.id=c.grade_id ) " +
                " LEFT JOIN video v ON ( cv.video_id = v.id  )" +
                " WHERE c.school_id = ? and g.other=0 GROUP BY c.id,v.id ORDER BY g.id";
        //统计学校的发布图片统计
        String picturesSql ="select 0 noticeCount, c.id classId , COUNT(1) picturesCount, 0 videoCount,p.opertime opertime from  classes c " +
                "left join class_pictures cp on(c.id=cp.class_id) LEFT JOIN grade g on(g.id=c.grade_id ) " +
                "left join  pictures p on(cp.pictures_id=p.id )" +
                " where c.school_id = ? and g.other=0 GROUP BY c.id,p.id ORDER BY g.id";
        //统计学校的发布通知统计
        String noticeSql ="select COUNT(1) noticeCount,c.id classId,0 picturesCount,0 videoCount,n.opertime opertime from classes c " +
                "left join class_notice cn on(c.id=cn.class_id) LEFT JOIN grade g on(g.id=c.grade_id ) " +
                "left join notice n on(cn.notice_id=n.id ) " +
                "where c.school_id = ? and g.other=0 GROUP BY c.id ,n.id ORDER BY g.id";

        //整合SQL
        String sql = "select  c.`name` className, if(sum( noticeCount ) is null,0,sum( noticeCount )) noticeCount," +
                " if(sum( picturesCount ) is null,0,sum( picturesCount )) picturesCount, " +
                " if(sum( videoCount ) is null,0,sum( videoCount ))  videoCount  " +
                " FROM classes c LEFT JOIN   ( ("+videoSql+") UNION ALL ("+picturesSql+") UNION ALL ("+noticeSql+")) a " +
                "on(c.id=a.classId "+time+") LEFT JOIN grade g ON ( g.id = c.grade_id ) where c.school_id=? AND g.other = 0  GROUP BY c.id";

        //视频,图片,通知统计
        List<Map<String,Object>> list = classPicturesJdbc.queryListMap(sql,schoolId,schoolId,schoolId,schoolId);

        return new Result(list);
    }
}
